﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using DSPL.DataAccess.MasterData;
using System.Data;
using DSPL.Contracts;
using DSPL.DataAccess;
using DSPL.Contracts.Common;

namespace DSPL.Services.Services
{
    public class MasterWriteService
    {

        public bool DeleteSupplierSpecificData(int primaryKeyValue, ref SqlTransaction sqlTran, Modes formMode = Modes.Delete)
        {
            var taxService = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            whereList.Add(new ColumnWithValue { ColumnName = "SupplierPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKeyValue });
            var getTaxQuery = new Query() { TableName = "fCreTax", QType = QueryType.SELECTALL, WhereClause = whereList.ToArray() };
            var data = taxService.ExcecuteSelect(getTaxQuery, ref sqlTran);

            DateTime delDate = DateTime.Now;


            foreach (DataRow drCreditorTax in data.Rows)
            {
                insertFieldList.Clear();

                foreach (DataColumn item in data.Columns)
                {
                    if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drCreditorTax[item.ColumnName] });
                    }
                }

                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelDate", ColumnValue = delDate });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelType", ColumnValue = (formMode == Modes.Delete ? "D" : "E") });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser });
                var insertQuery = new Query { TableName = "lCreTax", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (taxService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            // deletes the selected records from the child table
            var deleteTaxQuery = new Query() { TableName = "fCreTax", QType = QueryType.DELETE, WhereClause = whereList.ToArray() };
            if (taxService.Excecute(deleteTaxQuery, ref sqlTran) == null)
            {
                return false;
            }

            return true;
        }

        public bool DeleteDebtorSpecificData(int primaryKeyValue, ref SqlTransaction sqlTran, Modes formMode = Modes.Delete)
        {

            var taxService = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            whereList.Add(new ColumnWithValue { ColumnName = "DebPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKeyValue });
            var getTaxQuery = new Query() { TableName = "fDebTax", QType = QueryType.SELECTALL, WhereClause = whereList.ToArray() };
            var dataTax = taxService.ExcecuteSelect(getTaxQuery, ref sqlTran);

            DateTime delDate = DateTime.Now;

            foreach (DataRow drCreditorTax in dataTax.Rows)
            {
                insertFieldList.Clear();

                foreach (DataColumn item in dataTax.Columns)
                {
                    if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drCreditorTax[item.ColumnName] });
                    }
                }

                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelDate", ColumnValue = delDate });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelType", ColumnValue = (formMode == Modes.Delete ? "D" : "E") });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser });
                var insertQuery = new Query { TableName = "lDebTax", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (taxService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            // deletes the selected records from the 'fDebTax' child table
            var deleteTaxQuery = new Query() { TableName = "fDebTax", QType = QueryType.DELETE, WhereClause = whereList.ToArray() };
            if (taxService.Excecute(deleteTaxQuery, ref sqlTran) == null)
            {
                return false;
            }

            var getPriceLevelQuery = new Query() { TableName = "fDebPriLvl", QType = QueryType.SELECTALL, WhereClause = whereList.ToArray() };
            var dataPrice = taxService.ExcecuteSelect(getPriceLevelQuery, ref sqlTran);

            foreach (DataRow drPrice in dataPrice.Rows)
            {
                insertFieldList.Clear();

                foreach (DataColumn item in dataPrice.Columns)
                {
                    if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drPrice[item.ColumnName] });
                    }
                }

                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelDate", ColumnValue = delDate });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelType", ColumnValue = (formMode == Modes.Delete ? "D" : "E") });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser });
                var insertQuery = new Query { TableName = "lDebPriLvl", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (taxService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }

                // deletes the selected records from the 'fDebTax' child table
                var deletePriceQuery = new Query() { TableName = "fDebPriLvl", QType = QueryType.DELETE, WhereClause = whereList.ToArray() };
                if (taxService.Excecute(deleteTaxQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }


            return true;
        }


        public bool UpdateSupplierTaxData(DataRow supplierData, DataTable dtCreditorTax, ref SqlTransaction sqlTran, Modes formMode = Modes.Edit)
        {
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            var taxService = DatabaseAccess.Instance;
            int intKey = Convert.ToInt32(supplierData["SupplierPk"]);

            DeleteSupplierSpecificData(intKey, ref sqlTran, formMode);

            var itemPriceService = DatabaseAccess.Instance;

            if (Convert.ToInt32(supplierData["TaxReg"]) == 1)
            {

                foreach (DataRow drTCreditorTax in dtCreditorTax.Rows)
                {
                    insertFieldList.Clear();
                    if ((drTCreditorTax["TaxCode"]) == null || (drTCreditorTax["TaxCode"].ToString().Trim() == string.Empty))
                    {
                        continue;
                    }
                    insertFieldList.Add(new ColumnWithValue { ColumnName = "SupplierPk", ColumnValue = intKey });
                    insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxPk", ColumnValue = drTCreditorTax["TaxPk"] });
                    insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxRegNo", ColumnValue = drTCreditorTax["TaxRegNo"] });

                    var taxInsertQuery = new Query { TableName = "fCreTax", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                    if (taxService.Excecute(taxInsertQuery, ref sqlTran) == null)
                    {
                        return false;
                    }
                }
            }

            return true;            
        }

        public bool InsertSupplierSpecificData()
        {
            return true;
        }

        public bool DeleteProductSpecificData(int primaryKeyValue, ref SqlTransaction sqlTran, Modes formMode = Modes.Delete)
        {

            var itemPriceService = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            whereList.Add(new ColumnWithValue { ColumnName = "ItemPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKeyValue });
            var getItemPriceQuery = new Query() { TableName = "fItemPri", QType = QueryType.SELECTALL, WhereClause = whereList.ToArray() };
            var data = itemPriceService.ExcecuteSelect(getItemPriceQuery, ref sqlTran);
            DateTime delDate = DateTime.Now;

            foreach (DataRow drItemPrice in data.Rows)
            {
                insertFieldList.Clear();

                foreach (DataColumn item in data.Columns)
                {
                    if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drItemPrice[item.ColumnName] });
                    }
                }

                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelDate", ColumnValue = delDate });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelType", ColumnValue = (formMode == Modes.Delete ? "D" : "E") });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser });
                var insertQuery = new Query { TableName = "lItemPri", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (itemPriceService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            // deletes the selected records from the child table
            var deleteTaxQuery = new Query() { TableName = "fItemPri", QType = QueryType.DELETE, WhereClause = whereList.ToArray() };
            if (itemPriceService.Excecute(deleteTaxQuery, ref sqlTran) == null)
            {
                return false;
            }

            return true;
 
        }

        public bool UpdateProductPriceData(DataRow productData, DataTable dtPriceLevel, ref SqlTransaction sqlTran,Modes formMode=Modes.Edit)
        {
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            var priceService = DatabaseAccess.Instance;
            int intKey = Convert.ToInt32(productData["ItemPk"]);

            if (!DeleteProductSpecificData(intKey, ref sqlTran, formMode))
            {
                return false;
            }


            DateTime txnDate = DateTime.Now;

            foreach (DataRow drItemPrice in dtPriceLevel.Rows)
            {
                insertFieldList.Clear();
                if ((drItemPrice["PrilCode"]) == null || (drItemPrice["PrilCode"].ToString().Trim() == string.Empty))
                {
                    continue;
                }
                insertFieldList.Add(new ColumnWithValue { ColumnName = "ItemPk", ColumnValue = intKey });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "PriLvlPk", ColumnValue = drItemPrice["PriLvlPk"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "Price", ColumnValue = drItemPrice["Price"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "AddUser", ColumnValue = drItemPrice["AddUser"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "AddDate", ColumnValue = drItemPrice["AddDate"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "AddMach", ColumnValue = drItemPrice["AddMach"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TxnDate", ColumnValue = txnDate });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TxnUser", ColumnValue = GlobalVariables.LoggedInUser });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TxnMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });

                var insertQuery = new Query { TableName = "fItemPri", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (priceService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            return true;
           
        }

        public bool UpdateDebtorTaxData(DataRow debtorData, DataTable dtDebtorTax, ref SqlTransaction sqlTran, Modes formMode = Modes.Edit)
        {
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            var taxService = DatabaseAccess.Instance;
            int intKey = Convert.ToInt32(debtorData["debPk"]);

            DeleteDebtorSpecificData(intKey, ref sqlTran, formMode);

            List<ColumnWithValue> TaxDeleteConditions = new List<ColumnWithValue>();
            TaxDeleteConditions.Add(new ColumnWithValue { ColumnName = "DebPk", Comparison = QComparison.EQUALS, ColumnValue = intKey });
            var taxDeletionQuery = new Query { TableName = "fDebTax", QType = QueryType.DELETE, WhereClause = TaxDeleteConditions.ToArray() };
            if (taxService.Excecute(taxDeletionQuery, ref sqlTran) == null)
            {
                return false;
            }

            if (Convert.ToInt32(debtorData["TaxReg"]) == 1)
            {

                foreach (DataRow drTCreditorTax in dtDebtorTax.Rows)
                {
                    insertFieldList.Clear();
                    if ((drTCreditorTax["TaxCode"]) == null || (drTCreditorTax["TaxCode"].ToString().Trim() == string.Empty))
                    {
                        continue;
                    }
                    insertFieldList.Add(new ColumnWithValue { ColumnName = "DebPk", ColumnValue = intKey });
                    insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxPk", ColumnValue = drTCreditorTax["TaxPk"] });
                    insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxRegNo", ColumnValue = drTCreditorTax["TaxRegNo"] });

                    var taxInsertQuery = new Query { TableName = "fDebTax", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                    if (taxService.Excecute(taxInsertQuery, ref sqlTran) == null)
                    {
                        return false;
                    }
                }

            }

            return true;

        }

        public bool UpdateDebtorPriceData(DataRow debtorData, DataTable dtDebtorPrice, ref SqlTransaction sqlTran, Modes formMode = Modes.Edit)
        {
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            var priceLevelService = DatabaseAccess.Instance;
            int intKey = Convert.ToInt32(debtorData["DebPk"]);

            
            List<ColumnWithValue> PriceDeleteConditions = new List<ColumnWithValue>();
            PriceDeleteConditions.Add(new ColumnWithValue { ColumnName = "DebPk", Comparison = QComparison.EQUALS, ColumnValue = intKey });
            var priceDeletionQuery = new Query { TableName = "fDebPriLvl", QType = QueryType.DELETE, WhereClause = PriceDeleteConditions.ToArray() };
            if (priceLevelService.Excecute(priceDeletionQuery, ref sqlTran) == null)
            {
                return false;
            }

            foreach (DataRow drPrice in dtDebtorPrice.Rows)
            {
                insertFieldList.Clear();

                if (drPrice["CurCode"].ToString().Trim() == string.Empty)
                {
                    continue;
                }
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DebPk", ColumnValue = intKey });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "PriLvlPk", ColumnValue = drPrice["PriLvlPk"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "CurPk", ColumnValue = drPrice["CurPk"] });

                var priceInsertQuery = new Query { TableName = "fDebPriLvl", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (priceLevelService.Excecute(priceInsertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            return true;

        }

        public bool DeleteTaxCombinationSpecificData(int primaryKeyValue, ref SqlTransaction sqlTran, Modes formMode = Modes.Delete)
        {

            var taxService = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "fTaxDet.TaxComPk" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxPk" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTaxDet.TaxRate" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTaxDet.TaxSeq" });           
            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxPer" });


            whereList.Add(new ColumnWithValue { ColumnName = "fTaxDet.TaxComPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKeyValue });
            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fTaxDet", ToTable = "fTax", FromColumn = "TaxPk", ToColumn = "TaxPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });
            var getTaxQuery = new Query() { TableName = "fTaxDet", QType = QueryType.SELECT, WhereClause = whereList.ToArray() ,ColValues=columnList.ToArray(),JoinStructure=joinList.ToArray()};
            var dataTax = taxService.ExcecuteSelect(getTaxQuery, ref sqlTran);

            DateTime delDate = DateTime.Now;

            foreach (DataRow drTaxComb in dataTax.Rows)
            {
                insertFieldList.Clear();

                foreach (DataColumn item in dataTax.Columns)
                {
                    if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drTaxComb[item.ColumnName] });
                    }
                }

                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelDate", ColumnValue = delDate });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelType", ColumnValue = (formMode == Modes.Delete ? "D" : "E") });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser });
                var insertQuery = new Query { TableName = "lTaxDet", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (taxService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            // deletes the selected records from the 'fTaxDet' child table
            var deleteTaxQuery = new Query() { TableName = "fTaxDet", QType = QueryType.DELETE, WhereClause = whereList.ToArray() };
            if (taxService.Excecute(deleteTaxQuery, ref sqlTran) == null)
            {
                return false;
            }

            return true;
        }

        public bool UpdateTaxCombinationData(DataRow taxCombData, DataTable dtTaxCombDet, ref SqlTransaction sqlTran, Modes formMode = Modes.Edit)
        {
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            var taxService = DatabaseAccess.Instance;
            int intKey = Convert.ToInt32(taxCombData["TaxComPk"]);

            DeleteTaxCombinationSpecificData(intKey, ref sqlTran, formMode);


            foreach (DataRow drTaxComb in dtTaxCombDet.Rows)
            {
                insertFieldList.Clear();
                if ((drTaxComb["TaxCode"]) == null || (string.IsNullOrEmpty(drTaxComb["TaxCode"].ToString().Trim())) || Convert.ToInt32(drTaxComb["TaxRate"])==0)
                {
                    continue;
                }
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxComPk", ColumnValue = intKey });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxPk", ColumnValue = drTaxComb["TaxPk"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxRate", ColumnValue = drTaxComb["TaxRate"] });               
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxSeq", ColumnValue = drTaxComb["TaxSeq"] });
                

                var taxInsertQuery = new Query { TableName = "fTaxDet", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (taxService.Excecute(taxInsertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }
            
            return true;

        }
        

        public bool UpdateCostCentreAccounts(DataRow costData, DataTable dtCostAccounts, ref SqlTransaction sqlTran, Modes formMode = Modes.Edit)
        {
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            var costService = DatabaseAccess.Instance;
            int intKey = Convert.ToInt32(costData["CostPk"]);
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            whereList.Add(new ColumnWithValue {ColumnName="CostPk", Comparison=QComparison.EQUALS,ColumnValue=intKey });
            columnList.Add(new ColumnWithValue {ColumnName="AccCode"});
            
            var getCostAccountQuery = new Query { TableName ="fGlfAcc", QType = QueryType.SELECT, WhereClause =whereList.ToArray(),ColValues=columnList.ToArray()};

            DataTable dtAccountsSaved = (DataTable)costService.ExcecuteSelect(getCostAccountQuery,ref sqlTran);
            DataColumn[] primaryKeyColumns = new DataColumn[1];
            primaryKeyColumns[0] = dtAccountsSaved.Columns["AccCode"];
            dtAccountsSaved.PrimaryKey = primaryKeyColumns;

            DataTable dtAccountsToBeSaved = dtCostAccounts.Clone();

            foreach(DataRow drCostAccounts in dtCostAccounts.Rows)
            {
                string acccountCode = drCostAccounts["AccCode"].ToString().Trim();
                DataRow findRow = dtAccountsSaved.Rows.Find(acccountCode);

                if (Convert.ToInt32(drCostAccounts["Upd"]) == 1)
                {
                    if (findRow == null)
                    {
                        DataRow dtNewRow = dtAccountsToBeSaved.NewRow();

                        foreach (DataColumn dtc in dtCostAccounts.Columns)
                        {
                            dtNewRow[dtc.ColumnName] = drCostAccounts[dtc.ColumnName];
                        }

                        dtAccountsToBeSaved.Rows.Add(dtNewRow);
                    }
                }
                else
                {
                    if (findRow != null)
                    {
                        List<ColumnWithValue> deleteCondition = new List<ColumnWithValue>();
                        deleteCondition.Add(new ColumnWithValue { ColumnName = "AccCode", Comparison = QComparison.EQUALS, ColumnValue = acccountCode });
                        var deleteCostCentreAccountsQuery = new Query() { TableName = "fGlfAcc", QType = QueryType.DELETE, WhereClause = deleteCondition.ToArray() };
                        if (costService.Excecute(deleteCostCentreAccountsQuery, ref sqlTran) == null)
                        {
                            return false;
                        }
                    }
                }
                
            }
            DateTime txnDate = DateTime.Now;
            foreach (DataRow drAccounts in dtAccountsToBeSaved.Rows)
            {
                insertFieldList.Clear();
                if  (string.IsNullOrEmpty(drAccounts["AccCode"].ToString().Trim())) 
                {
                    continue;
                }
                insertFieldList.Add(new ColumnWithValue { ColumnName = "CostPk", ColumnValue = intKey });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "AccCode", ColumnValue = drAccounts["AccCode"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "AccDesc", ColumnValue = drAccounts["AccDesc"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "AccSortPk", ColumnValue = drAccounts["AccSortPk"]});
                insertFieldList.Add(new ColumnWithValue { ColumnName = "NoteCode", ColumnValue = drAccounts["NoteCode"]});
                insertFieldList.Add(new ColumnWithValue { ColumnName = "SubnNote", ColumnValue = drAccounts["SubnNote"]});
                insertFieldList.Add(new ColumnWithValue { ColumnName = "AccCat", ColumnValue = drAccounts["AccCat"]});
                insertFieldList.Add(new ColumnWithValue { ColumnName = "ConAcc", ColumnValue = drAccounts["ConAcc"]});
                insertFieldList.Add(new ColumnWithValue { ColumnName = "CurPk", ColumnValue = drAccounts["CurPk"]});
                insertFieldList.Add(new ColumnWithValue { ColumnName = "NumGen", ColumnValue = drAccounts["NumGen"]});
                insertFieldList.Add(new ColumnWithValue { ColumnName = "BankChg", ColumnValue = "" });                
                insertFieldList.Add(new ColumnWithValue { ColumnName = "AddUser", ColumnValue = GlobalVariables.LoggedInUser});
                insertFieldList.Add(new ColumnWithValue { ColumnName = "AddDate", ColumnValue = txnDate});
                insertFieldList.Add(new ColumnWithValue { ColumnName = "AddMach", ColumnValue = drAccounts["AddMach"] });               

                var costAccountInsertQuery = new Query { TableName = "fGlfAcc", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (costService.Excecute(costAccountInsertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }
            
            return true;

        }


        public bool DeleteCostCentreAccountDetails(int primaryKeyValue, ref SqlTransaction sqlTran, Modes formMode = Modes.Delete)
        {
            var costCentreService = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();
           
             whereList.Add(new ColumnWithValue { ColumnName = "CostPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKeyValue });
              var getAccountsQuery = new Query() { TableName = "fGlfAcc", QType = QueryType.SELECTALL, WhereClause = whereList.ToArray() };
             var   data = costCentreService.ExcecuteSelect(getAccountsQuery, ref sqlTran);
            
            DateTime delDate = DateTime.Now;

            foreach (DataRow drAccounts in data.Rows)
            {
                insertFieldList.Clear();

                foreach (DataColumn item in data.Columns)
                {
                    if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drAccounts[item.ColumnName] });
                    }
                }

                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelDate", ColumnValue = delDate });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelType", ColumnValue = (formMode == Modes.Delete ? "D" : "E") });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser });
                var insertQuery = new Query { TableName = "lGlfAcc", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (costCentreService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            // deletes the selected records from the child table
            var deleteTaxQuery = new Query() { TableName = "fGlfAcc", QType = QueryType.DELETE, WhereClause = whereList.ToArray() };
            if (costCentreService.Excecute(deleteTaxQuery, ref sqlTran) == null)
            {
                return false;
            }

            return true;

        }


        public bool UpdateCostData(int costLevelKey,string maxLevelFieldName,int maxLevelKey, SqlTransaction sqlTran)
        {
            var costCentreService = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue {ColumnName="CostPk",Comparison=QComparison.EQUALS,ColumnValue=maxLevelKey });
            List<ColumnWithValue> UpdateFieldList = new List<ColumnWithValue>();
            UpdateFieldList.Add(new ColumnWithValue { ColumnName = maxLevelFieldName, ColumnValue = maxLevelKey });
            var updateCostQuery = new Query() { TableName = "fCost", ColValues=UpdateFieldList.ToArray(), QType = QueryType.UPDATE, WhereClause = whereList.ToArray() };
            if (costCentreService.Excecute(updateCostQuery, ref sqlTran) == null)
            {
                return false;
            }

             return true;
        }
      

    }
}
 